import sqlite3, datetime
from django.shortcuts import render
from django.http import HttpResponse
from shared_resources import *

def staff_edit(request, staff_id):
	staff_id = int(staff_id)
	initialize(request)
	output = ""
	
	if not request.session["rights"]["edit_staff"] and staff_id != request.session["user"]["id"]:
		output += '<p class="bg-warning">'+t('You do not have the rights to edit staff.')+'</p>'
	else:
	
		sql_connection = sqlite3.connect(global_vars["database"])
		sql_cursor = sql_connection.cursor()    

		result = sql_cursor.execute("""select first_name, last_name, phone_0, phone_1, email, teacher, organizer, administrator from staff where id = ?""", (staff_id,)).fetchone()
		if result == None:
			output += '<div class="col-xs-12"><p class="bg-warning">'+t('Staff member does not exist')+'</p></div>'
		else:
			output += '<div class="col-xs-12">'
		
			if 'email' in request.POST.keys():
				values = (request.POST["first_name"], request.POST["last_name"], request.POST["phone_0"], request.POST["phone_1"], request.POST["email"], int(request.POST.get("teacher", 0)), int(request.POST.get("organizer", 0)), int(request.POST.get("administrator", 0)), to_int(staff_id))
				try:
					sql_cursor.execute("""Update staff set first_name = ?, last_name = ?, phone_0 = ?, phone_1 = ?, email = ?, teacher = ?, organizer = ?, administrator = ? where id = ?""", values)
					sql_connection.commit()
					output += '<p class="bg-success">'+t('User information updated.')+'</p>'	
					result = sql_cursor.execute("""select first_name, last_name, phone_0, phone_1, email, teacher, organizer, administrator from staff where id = ?""", (staff_id,)).fetchone()
				except:
					output += '<p class="bg-warning">'+t('Email address not unique.')+'</p>'
				
			if 'current_password' in request.POST.keys():
				if not test_password(result[4], request.POST["current_password"], 0):
					output += '<p class="bg-warning">'+t('Wrong password')+'</p>'
				elif request.POST["password1"] != request.POST["password2"]:
					output += '<p class="bg-warning">'+t('Passwords don\'t match')+'</p>'
				else:
					salt = random_hex()
					pass_hash = pass_derivation(request.POST["password1"], salt)
					sql_cursor.execute("""update staff set pass_hash = ?, pass_salt = ? where id = ?""", (pass_hash, salt, staff_id))
					sql_connection.commit()
					output += '<p class="bg-success">'+t('Password changed.')+'</p>'
				
			if 'reset' in request.POST.keys():
				output += reset_user_password(0, to_int(staff_id), result[4])
					
			output += (
			'<div class="row"><div class="col-xs-12 col-md-6">'
			'<form action="?" method="POST" name="account" id="account">'
			'<h3>'+t('Edit Staff Member')+'</h3>'
			'<table class="table newuser">'
			'<tr><td>'+t('First name: ')+'</td><td><input type="text" name="first_name" value="'+result[0]+'"></td></tr>'
			'<tr><td>'+t('Last name: ')+'</td><td><input type="text" name="last_name" value="'+result[1]+'"></td></tr>'
			'<tr><td>'+t('Phone number 1: ')+'</td><td><input type="text" name="phone_0" value="'+result[2]+'"></td></tr>'
			'<tr><td>'+t('Phone number 2: ')+'</td><td><input type="text" name="phone_1" value="'+result[3]+'"></td></tr>'
			'<tr><td>'+t('Email: ')+'</td><td><input type="text" name="email" value="'+result[4]+'" onkeyup="validate_email(document.forms.account.email.value)"/> <div id="email" style="display: inline;"></div></td></tr>'
			'<tr><td>'+t('Teacher: ')+'</td><td><input name="teacher" type="checkbox" value="1"'+(" checked" if result[5] == 1 else "")+'></td></tr>'
			'<tr><td>'+t('Organizer: ')+'</td><td><input name="organizer" type="checkbox" value="1"'+(" checked" if result[6] == 1 else "")+'></td></tr>'
			'<tr><td>'+t('Administrator: ')+'</td><td><input name="administrator" type="checkbox" value="1"'+(" checked" if result[7] == 1 else "")+'></td></tr>'
			'<tr><td></td><td><input class="btn btn-primary" type="submit" value="'+t('Edit')+'"></td></tr>'
			'</table>'
			'</form>'
			'</div>')

			if (request.session["user"]["id"] != staff_id):
				output += ('<a href="#" onclick="confirm_reset_password('+str(staff_id)+', \''+result[0]+'\')">'+t('Reset password')+'</a>')
			else:
				output += (
					'<div class="col-xs-12 col-md-6">'
					'<h3>'+t('Change Password')+'</h3>'
					'<form action="?" method="post" name="new_pass" id="new_pass">'
					'<table class="table newuser">'
					'<tr><td>'+t('Current Password')+'</td><td><input type="password" name="current_password"></td></tr>'
					'<tr><td>'+t('New Password')+'</td><td><input type="password" id="password1" name="password1" onkeyup="pass_str(document.forms.new_pass.password1.value);pass_mtc(document.forms.new_pass.password1.value, document.forms.new_pass.password2.value);"/><br /><div id="verdict" style="display: block;"></div></td></tr>'
					'<tr><td>'+t('Repeat New Password')+'</td><td><input type="password" id="password2" name="password2" onkeyup="pass_mtc(document.forms.new_pass.password1.value, document.forms.new_pass.password2.value)" /><div id="match" style="display: block;"></div></td></tr>'
					'<tr><td></td><td><input class="btn btn-primary" type="submit" value="'+t('Change')+'"></td></tr>'
					'</table>'
					'</form>'
					'</div>'
				)
			
				
			output += """
			<script>
				function confirm_reset_password(id, name){
					var retVal = confirm('"""+t('Are you sure you want to reset the password of %%s?')+"""'.replace("%s", name));
					if( retVal == true ){
						post_request({"reset": id}, "/office/staff-edit/"+id+"/");
						return true;
					}
				}
				function pass_str(pass){
					vericts = [
						"<img src='/static/icons/bad.png' /> <b  class='error'>Too weak</b>", 
						"<img src='/static/icons/good.png' /> <b style='color:#742400;display: inline;'>"""+t('Weak')+"""</b>",
						"<img src='/static/icons/good.png' /> <b style='color:#494900;display: inline;'>"""+t('Mediocre')+"""</b>",
						"<img src='/static/icons/good.png' /> <b style='color:#247400;display: inline;'>"""+t('Strong')+"""</b>",
						"<img src='/static/icons/good.png' /> <b style='color:#009900;display: inline;'>"""+t('Very strong')+"""</b>",
					];
					document.getElementById('verdict').innerHTML = vericts[password_strength(pass)];
				}
				function pass_mtc(pass1, pass2){
					document.getElementById('match').innerHTML =  (password_match(pass1, pass2) ? '<img src="/static/icons/good.png" /> <b style="color:#009900;display: inline;">"""+t('Passwords match')+"""</b>' : "<img src='/static/icons/bad.png' /> <b class='error'>"""+t("Passwords don't match")+"""</b>");
				}
			</script>
			</div>
			</div>
			"""
	
	return format_response(version["icons"]["staff"]+t("Edit Staff Member"), output)
	
def staff_info(request, staff_id):
	staff_id = int(staff_id)
	initialize(request)
	output = ""

	sql_connection = sqlite3.connect(global_vars["database"])
	sql_cursor = sql_connection.cursor()    
	
	header = ""
	
	result = sql_cursor.execute("""select first_name, last_name, phone_0, phone_1, email, teacher, organizer, administrator from staff where id = ?""", (staff_id,)).fetchone()
	if result == None:
		output += '<div class="col-xs-12"><p class="bg-warning">'+t('Staff member does not exist.')+'</p></div>'
	else:
		header = display_name(result[0],"",result[1])
		
		
		classes = []
		db_rows = sql_cursor.execute("""SELECT id, subject, description, (select count(student_id) from student_classes where class_id = classes.id), (select first_name from staff where id = classes.teacher_id), (select middle_name from staff where id = classes.teacher_id), (select last_name from staff where id = classes.teacher_id) FROM classes where teacher_id = ?""", (staff_id, ))
		for id, subject, description, student_count, first_name, middle_name, last_name in db_rows:
			classes.append({"id": id, "subject": class_subjects[subject], "description": description, "student_count": student_count, "teacher_name": display_name(first_name, middle_name, last_name)})
		
		output += '<h3>'+t('Current Classes')+'</h3><table class="table viewuser"><tr><th>'+t('Teacher')+'</th><th>'+t('Subject')+'</th><th>'+t('Students')+'</th><th>'+t('Student count')+'</th><th>'+t('Description')+'</th><th>'+t('Rating')+'</th><th>'+t('Edit')+'</th></tr>'
		total_sum = 0
		total_i = 0
		for key, class_values in enumerate(classes):
			i = 0
			sum = 0
			db_rows = sql_cursor.execute("""select rating from teacher_feedback where (select class_id from lessons where id = lesson_id) = ?""", (class_values["id"], ))
			for row in db_rows:
				sum += row[0]
				i += 1
			student_names = []
			db_rows = sql_cursor.execute("""select (select first_name from students where students.id = student_id), (select middle_name from students where students.id = student_id), (select last_name from students where students.id = student_id) from student_classes where class_id = ?""", (class_values["id"], ))
			for first_name, middle_name, last_name in db_rows:
				student_names.append(display_name(first_name, middle_name, last_name))
			output += ('<tr><td>'+class_values["teacher_name"]+'</td><td>'+class_values["subject"]+'</td><td>'+', '.join(student_names[:3]) + ("..." if len(student_names) > 2 else "")+'</td><td>'+str(class_values["student_count"])+'</td><td>'+class_values["description"]+'</td><td>'+(str(round(sum/float(i), 1))+'<div id="rating_'+str(key)+'"></div><script>update_stars("rating_'+str(key)+'", '+str(round(sum/float(i)))+');</script>' if i > 0 else t("None"))+'</td><td><a href="/office/class-edit/'+str(class_values["id"])+'/">'+version["icons"]["edit"]+'</a>'
			'<a href="/office/class-schedule/'+str(class_values["id"])+'/">'+version["icons"]["schedule2"]+'</a>'
			'</td></tr>')
			
			total_sum += sum
			total_i += i
			
		output += """</table>"""
	
		if request.session["rights"]["view_staff"]:
			
			today = datetime.datetime.now()
			week_first = today + datetime.timedelta(days=-today.weekday())
			week_first = datetime.datetime(week_first.year, week_first.month, week_first.day)
			week_last = int(time.mktime((week_first + datetime.timedelta(days=+7)).timetuple()))
			week_first = int(time.mktime((week_first).timetuple()))
			month_first = datetime.datetime(today.year, today.month, 1)
			month_last = int(time.mktime(datetime.datetime(month_first.year, month_first.month + 1, 1).timetuple()))
			month_first = int(time.mktime(month_first.timetuple()))
			
			month_sum = 0
			week_sum = 0
			for class_values in classes:
				db_rows = sql_cursor.execute("""SELECT start_time, end_time FROM lessons where class_id = ? and start_time > ? and end_time < ?""", (class_values["id"], month_first, month_last))
				for row in db_rows:
					month_sum += row[1] - row[0]
				db_rows = sql_cursor.execute("""SELECT start_time, end_time FROM lessons where class_id = ? and start_time > ? and end_time < ?""", (class_values["id"], week_first, week_last))
				for row in db_rows:
					week_sum += row[1] - row[0]
			
			output += (
			'<h3>'+t('Total lessons:')+'</h3>'
			'<table>'
			'<tr><td style="width: 200px;">'+t('Total lessons this week: ')+'</td><td>'+stamp_to_time_amount(week_sum)+'.</td></tr>'
			'<tr><td>'+t('Total lessons this month: ')+'</td><td>'+stamp_to_time_amount(month_sum)+'.</td></tr>'
			'<tr><td>'+t('Average rating: ')+'</td><td>'+(str(round(total_sum/float(total_i), 1))+' <div id="rating_total" style="display: inline; font-size: 22px;"></div><script>update_stars("rating_total", '+str(round(total_sum/float(total_i)))+');</script>' if total_i > 0 else t("No ratings yet"))+'.</td></tr>'
			'</table>')
		
		output += '<br /><br /><h3>'+t('Student feedback:')+'</h3>'
		t("No comments yet.")
		for class_values in classes:
			output += '<br /><br /><b>'+class_values["subject"]+' - '+class_values["description"]+'</b><br /><br />'
			db_rows = sql_cursor.execute("""select comment, time from teacher_feedback where (select class_id from lessons where id = lesson_id) = ? order by time desc""", (class_values["id"], ))
			no_rows = True
			for comment, timestamp in db_rows:
				if comment != "":
					output += '<div class="alert alert-warning alert-dismissible"><b>'+stamp_to_display(timestamp, "date_time_format")+'</b><br />'+comment + '</div>'
					no_rows = False
			if no_rows:
				output += t("No comments yet.")


		output += '<br /><br /><h3>'+t('Feedback given to students:')+'</h3>'
		
		for class_values in classes:
			output += '<br /><br /><b>'+class_values["subject"]+' - '+class_values["description"]+'</b><br /><br />'
			db_rows = sql_cursor.execute("""select comment, time, (select first_name from students where id = student_id), (select middle_name from students where id = student_id), (select last_name from students where id = student_id) from student_feedback where (select class_id from lessons where id = lesson_id) = ? order by time desc""", (class_values["id"], ))
			no_rows = True
			for comment, timestamp, first_name, middle_name, last_name in db_rows:
				if comment != "":
					output += '<div class="alert alert-warning alert-dismissible"><b>'+stamp_to_display(timestamp, "date_time_format")+'</b><br />'+display_name(first_name, middle_name, last_name)+': '+ comment + '</div>'
					no_rows = False
			if no_rows:
				output += t("No comments yet.")
	return format_response(version["icons"]["staff"]+header, output)

def staff_overview(request):
	initialize(request)
	output = ""
		
	if not request.session["rights"]["view_staff"]:
		output += '<p class="bg-warning">'+t('You do not have the rights to view staff.')+'</p>'
	else:
	
		sql_connection = sqlite3.connect(global_vars["database"])
		sql_cursor = sql_connection.cursor()    
		
		if 'delete' in request.POST.keys():
			if not request.session["rights"]["delete_staff"]:
				output += '<p class="bg-warning">'+t('You do not have the rights to delete staff.')+'</p>'
			else:
				result = sql_cursor.execute("""select first_name, last_name from staff where id = ?""", (request.POST["delete"],)).fetchone()
				log_message(sql_cursor, t("Staff deleted (%s)", (display_name(result[0],"",result[1]))))
				db_rows = sql_cursor.execute("""SELECT id FROM classes where teacher_id = ?""", (request.POST["delete"],))
				classes = []
				for row in db_rows:
					classes.append(row[0])

				lessons = []
				for class_id in classes:
					db_rows = sql_cursor.execute("""SELECT id FROM lessons where class_id = ?""", (class_id,))
					for row in db_rows:
						lessons.append(row[0])
					sql_cursor.execute("""delete from lessons where class_id = ?""", (class_id,))
					sql_cursor.execute("""delete from student_classes where class_id = ?""", (class_id,))
					sql_cursor.execute("""delete from classes where id = ?""", (class_id,))
				for lesson in lessons:
					sql_cursor.execute("""delete from student_feedback where lesson_id = ?""", (lesson,))
					sql_cursor.execute("""delete from teacher_feedback where lesson_id = ?""", (lesson,))
				sql_cursor.execute("""delete from staff where id = ?""", (request.POST["delete"],))
				sql_connection.commit()

				output += '<p class="bg-success">'+t('Staff member %s deleted.', (display_name(result[0],"",result[1])))+'</p>'
		
		if 'active' in request.POST.keys():
			sql_cursor.execute("""update classes set active = ? where teacher_id = ?""", (request.POST["active"], request.POST["id"]))
			sql_cursor.execute("""update staff set active = ? where id = ?""", (request.POST["active"], request.POST["id"]))
			sql_connection.commit()

		if 'email' in request.POST.keys():
			password = random_hex()[:8]
			salt = random_hex()
			
			pass_hash = pass_derivation(password, salt)
			
			email = request.POST.get('email', '')
			token = random_hex()[:8]
			
			values = (
				request.POST.get('first_name', ''),
				request.POST.get('middle_name', ''),
				request.POST.get('last_name', ''),
				request.POST.get('phone_0', ''),
				request.POST.get('phone_1', ''),
				email,
				token,
				to_int(request.POST.get('teacher', '0')),
				to_int(request.POST.get('organizer', '0')),
				to_int(request.POST.get('administrator', '0')),
				pass_hash,
				salt,
			)
			try:
				sql_cursor.execute("""INSERT INTO staff ('first_name', 'middle_name', 'last_name', 'phone_0', 'phone_1', 'email', 'email_token', 'teacher', 'organizer', 'administrator', 'pass_hash', 'pass_salt') VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)""", values)
				sql_connection.commit()
				output += '<p class="bg-success">'+t('New staff member created, password: %s.', (password))+'</p>'
				if not send_email(email, t(global_vars["mail_messages"]["new_user"][0], (), True), t(global_vars["mail_messages"]["new_user"][1], (global_vars["url"]+'/users/verify-email/'+token+'/', password), True)):
					output += '<p class="bg-warning">'+t('Error sending email to user!')+'</p>'

			except:
				output += '<p class="bg-warning">'+t('Email already exists')+'</b>'
				

		if request.session['show_disabled']:
			db_rows = sql_cursor.execute("""SELECT id, first_name, middle_name, last_name, administrator, organizer, teacher, active FROM staff order by active desc, first_name desc, last_name""")
		else:
			db_rows = sql_cursor.execute("""SELECT id, first_name, middle_name, last_name, administrator, organizer, teacher, active FROM staff where active = 1 order by active, first_name desc, last_name""")
		output += '<div class="row"><div class="col-xs-12 col-md-6"><h3>'+t('Current Staff Members')+'</h3><table class="viewuser table table-striped table-hover"><tr><th>'+t('Name')+'</th><th>'+t('Admin')+'</th><th>'+t('Organizer')+'</th><th>'+t('Teacher')+'</th><th style="text-align: right;"></th></tr>'

		for id, first_name, middle_name, last_name, administrator, organizer, teacher, active in db_rows:
			name = display_name(first_name, middle_name, last_name)
			output += ('<tr'+('' if active else ' class="disabled_row"')+'><td>'+name+'</td><td>'+("" if administrator != 1 else "&#10004;")+'</td><td>'+("" if organizer != 1 else "&#10004;")+'</td><td>'+("" if teacher != 1 else "&#10004;")+'</td><td style="text-align: right;">'
			'<a href="/office/lesson-overview/'+str(id)+'/">'+version["icons"]["list"]+'</a>'
			'<a href="/office/staff-info/'+str(id)+'/">'+version["icons"]["info"]+'</a>'
			'<a href="/office/staff-edit/'+str(id)+'/">'+version["icons"]["edit"]+'</a>'
			'<a href="/office/staff-schedule/'+str(id)+'/">'+version["icons"]["schedule2"]+'</a>'
			'<a href="/office/teacher-default-availability/'+str(id)+'/">'+version["icons"]["default"]+'</a>'
			'<a href="#" onclick="post_request({\'active\': '+str(1 - active)+', \'id\': '+str(id)+'});">'+(version["icons"]["enable"] if int(active) == 0 else version["icons"]["disable"])+'</a>'
			'<a href="#" onclick="confirm_delete(\''+str(id)+'\', \''+name+'\')">'+version["icons"]["delete"]+'</a>'
			'</td></tr>')
		
		output += '</table><button class="btn btn-primary" style="display: inline; margin-top: 10px; float: right;" type="submit" onclick="post_request({\'show_disabled\': '+str(1 - int(request.session['show_disabled']))+'});">'+(t('Hide disabled', (), True) if request.session['show_disabled'] else t('Show disabled', (), True))+'</button>'
		
		output += (
		'</div><div class="col-xs-12 col-md-6">'
		'<h3>'+t('Create Staff Member')+'</h3>'
		'<form action="?" method="POST" name="account">'
		'<table class="newuser table">'
		'<tr><td>'+t('First name: ')+'</td><td><input type="text" name="first_name"></td></tr>'
		'<tr><td>'+t('Last name: ')+'</td><td><input type="text" name="last_name"></td></tr>'
		'<tr><td>'+t('Phone number 1: ')+'</td><td><input type="text" name="phone_0"></td></tr>'
		'<tr><td>'+t('Phone number 2: ')+'</td><td><input type="text" name="phone_1"></td></tr>'
		'<tr><td>'+t('Email: ')+'</td><td><input type="text" name="email" onkeyup="update_email(document.forms.account.email.value)"/><br /><div id="email" style="display: inline;"></div></td></tr>'
		'<tr><td>'+t('Teacher: ')+'</td><td><input name="teacher" type="checkbox" value="1"></td></tr>'
		'<tr><td>'+t('Organizer: ')+'</td><td><input name="organizer" type="checkbox" value="1"></td></tr>'
		'<tr><td>'+t('Administrator: ')+'</td><td><input name="administrator" type="checkbox" value="1"></td></tr>'
		'<tr><td></td><td><input class="btn btn-primary" type="submit" value="'+t('Create')+'"></td></tr></table>'
		'</form>')
		output += """
		<script>
			function confirm_delete(id, name){
				var retVal = confirm('"""+t('Are you sure you want to delete staff member %%s? This will also delete his/her classes.')+"""'.replace("%s", name));
				if( retVal == true ){
					post_request({"delete": id});
					return true;
				}
			}
			function update_email(email){
				if(validate_email(email)){
					document.getElementById('email').innerHTML = '<img src="/static/icons/good.png" /> """+t('Valid email')+"""';
				}else{
					document.getElementById('email').innerHTML = '<img src="/static/icons/bad.png" /> """+t('Invalid email')+"""';
				}
			}
		</script>
		"""

		output += '</div></div>'    
    
	return format_response(version["icons"]["staff"]+t("Staff"), output)
	
def staff_schedule(request, staff_id, selected_week):
	
	initialize(request)
	output = ""

	header = t("Schedule")
	
	has_rights = request.session["rights"]["view_staff"] or int(staff_id) == request.session["user"]["id"]
	if not has_rights:
		output += '<p class="bg-warning">'+t('You do not have the rights to view this staff member.')+'</p>'
	else:
		
		sql_connection = sqlite3.connect(global_vars["database"])
		sql_cursor = sql_connection.cursor()    
				
		result = sql_cursor.execute("""select first_name, last_name from staff where id = ?""", (staff_id,)).fetchone()
		if result == None:
			output += '<div class="col-xs-12"><p class="bg-warning">'+t('Staff member does not exist')+'</p></div>'
		else:
			output += schedule_edit(staff_id)

			teacher_info = {"id": staff_id, "name": result[0]}
			
			request.session['schedule_week'] = int(selected_week) if selected_week != None else request.session['schedule_week']
			
			granularity = request.session['schedule_granularity']

			i = 0
			schedule = {0:{},1:{},2:{}}
			# Default schedule
			db_rows = sql_cursor.execute("""SELECT start_time, end_time, status FROM teacher_default_schedule where teacher_id = ?""", (staff_id,))
			for row in db_rows:
				schedule[0][i] = {"id": str(i), "start_time": row[0], "end_time": row[1], "type": row[2], "editable": 0, "layer": -1}
				i += 1

			# Weekly schedule
			week_sum = 0
			db_rows = sql_cursor.execute("""SELECT id, start_time, end_time, status FROM teacher_schedule where teacher_id = ? and start_time >= ? and end_time <= ?""", (staff_id, week_to_stamp(request.session['schedule_week']), week_to_stamp(request.session['schedule_week']+1)))
			for row in db_rows:
				schedule[1][row[0]] = {"id": row[0], "start_time": row[1] - week_to_stamp(request.session['schedule_week']), "end_time": row[2] - week_to_stamp(request.session['schedule_week']), "type": row[3], "editable": 1, "layer": -1}
			
			# Lesson schedule
			classes = {}
			has_editable_classes = False
			db_rows = sql_cursor.execute("""SELECT id, subject, description, edit_mode, active FROM classes where teacher_id = ?""", (staff_id,))

			for id, subject, description, edit_mode, active in db_rows:
				if request.session["rights"]["edit_staff"]:
					can_edit = 2
				elif (int(staff_id) == request.session["user"]["id"] and int(edit_mode) == 1):
					can_edit = 1
				else:
					can_edit = 0
					
				classes[id] = {"description": class_subjects[subject]+" - "+description, "can_edit": can_edit, 'active': active}
				if can_edit > 0:
					has_editable_classes = True

			lessons = {}
			for student_class, description in classes.iteritems():
				db_rows = sql_cursor.execute("""select start_time, end_time, (select teacher_id from classes where id = lessons.class_id), id, lessons.room_id, (select subject from classes where id = lessons.class_id), (select first_name from staff where id = (select teacher_id from classes where id = lessons.class_id)), class_id, confirmed from lessons where class_id = ? and start_time >= ? and end_time <= ?""", (student_class, week_to_stamp(request.session['schedule_week']), week_to_stamp(request.session['schedule_week']+1)))
				for row in db_rows:
					week_sum += int(row[1]) - int(row[0])
					type = (3 if row[8] == 1 else 4)
					can_confirm = request.session["rights"]["edit_staff"] or int(staff_id) == request.session["user"]["id"]
					schedule[2][row[3]] = {"id": row[3], "start_time": row[0] - week_to_stamp(request.session['schedule_week']), "end_time": row[1] - week_to_stamp(request.session['schedule_week']), "type": type, "editable": 1, "layer": -1}
					lessons[row[3]] = {"id":row[3], "room_id":row[4], "subject_id":row[5], "teacher_name":row[6], "class_id":row[7], "can_confirm": can_confirm}
			
			for id, value in classes.iteritems():
				student_names = []
				db_rows = sql_cursor.execute("""select (select first_name from students where id = student_id) from student_classes where class_id = ?""", (id,))
				for row in db_rows:
					student_names.append(row[0])
				classes[id]["students"] = ', '.join(student_names[:2]) + ("..." if len(student_names) > 2 else "")
			
			rooms = {}
			db_rows = sql_cursor.execute("""SELECT id, name FROM classrooms""")
			for row in db_rows:
				rooms[row[0]] = {"name": row[1], "unavailable": {}}
			
			db_rows = sql_cursor.execute("""select room_id, id, start_time, end_time from lessons where room_id != -1 and (select teacher_id from classes where classes.id = class_id) != ? and start_time >= ? and end_time <= ?""", (staff_id, week_to_stamp(request.session['schedule_week']), week_to_stamp(request.session['schedule_week']+1)))
			for row in db_rows:
				rooms[row[0]]["unavailable"][row[1]] = {"start_time": row[2] - week_to_stamp(request.session['schedule_week']), "end_time": row[3] - week_to_stamp(request.session['schedule_week'])}
			
			header = t('Schedule for %s', (display_name(result[0],"",result[1])))
			if has_rights:
				output += '<br /><p style="text-align: center;">'
				if not has_editable_classes:
					output += '<script>alert_message("'+t('<strong>Note:</strong> Teacher doesn\'t have editable classes, so can not create lessons.')+'", "info");</script>'
				output += t('Total length of lessons this week: %s', (stamp_to_time_amount(week_sum)))+'</p><br />'

			output += schedule_html((2 if has_editable_classes else 3), schedule, "/office/staff-schedule/"+staff_id, lessons, rooms, classes, teacher_info)
	
	return format_response(version["icons"]["staff"]+header, output)
